const initSqlJs = require('sql.js');
const path = require('path');
const fs = require('fs');

// 数据库配置
const DB_DIR = path.join(__dirname, 'data');
const DB_PATH = path.join(DB_DIR, 'medical_data.db');

// 确保数据目录存在
if (!fs.existsSync(DB_DIR)) {
    fs.mkdirSync(DB_DIR, { recursive: true });
    console.log('数据目录已创建:', DB_DIR);
}

// 数据库连接实例
let db = null;
let SQL = null;

/**
 * 初始化数据库连接
 */
async function initDatabase() {
    try {
        // 初始化sql.js
        SQL = await initSqlJs();
        
        // 尝试加载现有数据库文件
        let filebuffer = null;
        if (fs.existsSync(DB_PATH)) {
            filebuffer = fs.readFileSync(DB_PATH);
            console.log('加载现有数据库文件:', DB_PATH);
        } else {
            console.log('创建新数据库文件:', DB_PATH);
        }
        
        // 创建数据库实例
        db = new SQL.Database(filebuffer);
        console.log('SQLite数据库连接成功');
        
        // 创建表结构
        createTables();
        console.log('数据库表结构初始化完成');
        
        // 保存数据库到文件
        saveDatabase();
        
        return db;
    } catch (error) {
        console.error('数据库初始化错误:', error);
        throw error;
    }
}

/**
 * 创建数据表
 */
function createTables() {
    const createTableSQL = `
        CREATE TABLE IF NOT EXISTS medical_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            timestamp INTEGER NOT NULL,
            data_type TEXT NOT NULL,
            value REAL NOT NULL,
            device_id TEXT DEFAULT 'default',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    `;
    
    try {
        db.run(createTableSQL);
        
        // 创建索引
        db.run('CREATE INDEX IF NOT EXISTS idx_timestamp ON medical_data(timestamp)');
        db.run('CREATE INDEX IF NOT EXISTS idx_data_type ON medical_data(data_type)');
        db.run('CREATE INDEX IF NOT EXISTS idx_created_at ON medical_data(created_at)');
        
        console.log('medical_data表创建成功');
    } catch (error) {
        console.error('创建数据表失败:', error.message);
        throw error;
    }
}

/**
 * 保存数据库到文件
 */
function saveDatabase() {
    try {
        const data = db.export();
        const buffer = Buffer.from(data);
        fs.writeFileSync(DB_PATH, buffer);
    } catch (error) {
        console.error('保存数据库失败:', error.message);
    }
}

/**
 * 获取数据库连接
 */
function getDatabase() {
    if (!db) {
        throw new Error('数据库未初始化，请先调用initDatabase()');
    }
    return db;
}

/**
 * 保存医疗数据
 */
async function saveData(dataType, value, timestamp = Date.now(), deviceId = 'default') {
    try {
        if (!db) {
            throw new Error('数据库未初始化');
        }
        
        // 数据验证
        if (!dataType || typeof value !== 'number' || isNaN(value)) {
            throw new Error('无效的数据格式');
        }
        
        const insertSQL = `
            INSERT INTO medical_data (timestamp, data_type, value, device_id)
            VALUES (?, ?, ?, ?)
        `;
        
        const stmt = db.prepare(insertSQL);
        stmt.run([timestamp, dataType, value, deviceId]);
        
        // 保存到文件
        saveDatabase();
        
        console.log(`数据保存成功: ${dataType}=${value}`);
        return {
            timestamp,
            dataType,
            value,
            deviceId
        };
    } catch (error) {
        console.error('数据保存失败:', error.message);
        throw error;
    }
}/**
 * 查询医疗数据
 */
async function queryData(options = {}) {
    try {
        if (!db) {
            throw new Error('数据库未初始化');
        }

        const {
            dataTypes = [],
            startTime = 0,
            endTime = Date.now(),
            limit = 1000,
            offset = 0,
            deviceId = null
        } = options;

        // 构建基础WHERE条件
        let whereClause = `WHERE timestamp >= ? AND timestamp <= ?`;
        let params = [startTime, endTime];

        // 添加数据类型筛选
        if (dataTypes.length > 0) {
            const placeholders = dataTypes.map(() => '?').join(',');
            whereClause += ` AND data_type IN (${placeholders})`;
            params.push(...dataTypes);
        }

        // 添加设备ID筛选
        if (deviceId) {
            whereClause += ` AND device_id = ?`;
            params.push(deviceId);
        }

        // 首先查询总数
        const countSql = `SELECT COUNT(*) as total FROM medical_data ${whereClause}`;
        const countStmt = db.prepare(countSql);
        countStmt.bind(params);

        let totalCount = 0;
        if (countStmt.step()) {
            totalCount = countStmt.getAsObject().total;
        }
        countStmt.free();

        // 然后查询分页数据
        const dataSql = `
            SELECT id, timestamp, data_type, value, device_id, created_at
            FROM medical_data
            ${whereClause}
            ORDER BY timestamp DESC LIMIT ? OFFSET ?
        `;
        const dataParams = [...params, limit, offset];

        const dataStmt = db.prepare(dataSql);
        dataStmt.bind(dataParams);
        const rows = [];

        while (dataStmt.step()) {
            rows.push(dataStmt.getAsObject());
        }

        dataStmt.free();

        // 返回数据和总数
        return {
            data: rows,
            total: totalCount
        };
    } catch (error) {
        console.error('数据查询失败:', error.message);
        throw error;
    }
}

/**
 * 删除医疗数据
 */
async function deleteData(options = {}) {
    try {
        if (!db) {
            throw new Error('数据库未初始化');
        }
        
        const {
            dataTypes = [],
            startTime = 0,
            endTime = Date.now(),
            deviceId = null
        } = options;
        
        let sql = `DELETE FROM medical_data WHERE timestamp >= ? AND timestamp <= ?`;
        let params = [startTime, endTime];
        
        // 添加数据类型筛选
        if (dataTypes.length > 0) {
            const placeholders = dataTypes.map(() => '?').join(',');
            sql += ` AND data_type IN (${placeholders})`;
            params.push(...dataTypes);
        }
        
        // 添加设备ID筛选
        if (deviceId) {
            sql += ` AND device_id = ?`;
            params.push(deviceId);
        }
        
        // 先查询要删除的记录数
        const countSql = sql.replace('DELETE FROM', 'SELECT COUNT(*) as count FROM');
        const countStmt = db.prepare(countSql);
        countStmt.bind(params);

        let deletedCount = 0;
        if (countStmt.step()) {
            deletedCount = countStmt.getAsObject().count;
        }
        countStmt.free();

        // 执行删除操作
        const stmt = db.prepare(sql);
        stmt.bind(params);
        stmt.step();
        stmt.free();

        // 保存到文件
        saveDatabase();

        console.log(`数据删除成功，删除了 ${deletedCount} 条记录`);
        return {
            success: true,
            deletedCount: deletedCount
        };
    } catch (error) {
        console.error('数据删除失败:', error.message);
        throw error;
    }
}

/**
 * 关闭数据库连接
 */
async function closeDatabase() {
    try {
        if (db) {
            // 保存数据库
            saveDatabase();
            db.close();
            console.log('数据库连接已关闭');
            db = null;
        }
    } catch (error) {
        console.error('数据库关闭失败:', error.message);
        throw error;
    }
}

/**
 * 测试数据库连接
 */
async function testConnection() {
    try {
        if (!db) {
            throw new Error('数据库未初始化');
        }
        
        const stmt = db.prepare('SELECT 1 as test');
        stmt.step();
        const result = stmt.getAsObject();
        
        console.log('数据库连接测试成功');
        return result;
    } catch (error) {
        console.error('数据库连接测试失败:', error.message);
        throw error;
    }
}

module.exports = {
    initDatabase,
    getDatabase,
    saveData,
    queryData,
    deleteData,
    closeDatabase,
    testConnection
};